select sla_service as service,
avg(sla_exec_millis) as moyenne_temps_exec,
sum(sla_exec_millis) as sum_tps_exec, 
sum(sla_exec_millis)/24 as tps_exec_perhour 
from t_auditoutline group by service order by 1;
----------------------------------------------------------

dblogs=# CREATE OR REPLACE FUNCTION fib (
        fib_for integer
    ) RETURNS integer AS $$
   BEGIN
        IF fib_for < 2 THEN
            RETURN fib_for;
        END IF;
        RETURN fib(fib_for - 2) + fib(fib_for - 1);
    END;
  $$ LANGUAGE plpgsql;
CREATE FUNCTION
          ^
dblogs=# select fib(8);
 fib 
-----
  21
(1 ligne)
----------------------------------------------
CREATE TABLE fib_cache (
 2        num integer PRIMARY KEY,
 3        fib integer NOT NULL
 4   );
 5
 6   CREATE OR REPLACE FUNCTION fib_cached(
 7       fib_for integer
 8   ) RETURNS integer AS $$
 9   DECLARE
10      ret integer;
11  BEGIN
12      if fib_for < 2 THEN
13          RETURN fib_for;
14      END IF;
15
16      SELECT INTO ret fib
17      FROM   fib_cache
18      WHERE  num = fib_for;
19
20      IF ret IS NULL THEN
21          ret := fib_cached(fib_for - 2) + fib_cached(fib_for - 1);
22          INSERT INTO fib_cache (num, fib)
23          VALUES (fib_for, ret);
24      END IF;
25      RETURN ret;
26
27  END;
28  $$ LANGUAGE plpgsql;

------------------------------------
CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

-------------------------------------
create or replace function fn_nb_appel(service text, dateDebutAppelService timestamp, dateFinAppelService timestamp)
returns integer as $$
	BEGIN
        IF (dateDebutAppelService < (select  min(sla_date) from t_auditoutline )
  			OR 
  			dateFinAppelService > (select  max(sla_date) from t_auditoutline))
  			then RETURN -1;
  		END IF;
  		
  		
  		SELECT COUNT(sla_service) as nb_appel_serv, count(id) as nb_appel_tte_serv  from t_auditoutline
  				where sla_date between dateDebutAppelService and dateFinAppelService and sla_service='service';
  		return nb_appel_serv/nb_appel_tte_serv*100;
  		   
     END;
$$LANGUAGE plpgsql;


--------------------------------------------------------
returns integer as $$
	BEGIN
        IF (dateDebutAppelService < (select  min(sla_date) from t_auditoutline )
  			OR 
  			dateFinAppelService > (select  max(sla_date) from t_auditoutline))
  			then RETURN -1;
  		END IF;
  		
  		
  		SELECT (( COUNT(sla_service) / count(id))*100)  from t_auditoutline
  				where sla_date between dateDebutAppelService and dateFinAppelService and sla_service='AQ';
  		nb_appel_serv/nb_appel_tte_serv*100;
  		  
     END;
$$LANGUAGE plpgsql;

select count(sla_service)/count(id)*100 from t_auditoutline
	where sla_service in (select sla_service from t_auditoutline 
	where sla_date between dateDebutAppelService and dateFinAppelService and sla_service ='AQ' ) and id in (select id from t_auditoutline where 
	sla_date between dateDebutAppelService and dateFinAppelService
	

select count(sla_service)/count(id)*100 from t_auditoutline
	where sla_service in (select sla_service from t_auditoutline 
	where sla_date between '2013-06-29 16:38:35.393+0200' and '2013-06-29 16:38:47.112+0200' and sla_service ='AQ' ) and id in (select id from t_auditoutline where 
	sla_date between '2013-06-29 16:38:35.393+0200' and '2013-06-29 16:38:47.112+0200'

select count(select sla_service from t_auditoutline here sla_date between '2013-06-29 16:38:35.393+0200' and '2013-06-29 16:38:47.112+0200')/count(select id from t_auditoutline where 
	sla_date between '2013-06-29 16:38:35.393+0200' and '2013-06-29 16:38:47.112+0200')*100 from t_auditoutline);





	where sla_service in (select sla_service from t_auditoutline 
	where sla_date between '2013-06-29 16:38:35.393+0200' and '2013-06-29 16:38:47.112+0200' and sla_service ='AQ' ) and id in (select id from t_auditoutline where 
	sla_date between '2013-06-29 16:38:35.393+0200' and '2013-06-29 16:38:47.112+0200'

select avg(sla_exec_millis) as moyenne_temps_exec,sum(sla_exec_millis) as sum_tps_exec from t_auditoutline where sla_date between '2013-06-29 16:38:32.098+0200' and '2013-07-01 00:43:44.860+0200' and sla_service='service';

//Nobre d''appel par heure:

heure(Date fin -date debut) corespond a totalAppel

select sla_code as code from t_auditoutline
	SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

create or replace function()
 returns Integer as
Begin &&
select sla_code,
	case when sla_code="030" then  
	case when sla_code="060" then 
	case when sla_code='N' then
	else 'Autres Erreurs'
	end
	from t_auditoutline;
	
	END;
&&
$$LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION fib_cached(service String, dateDebut String ,  dateFin ) RETURNS integer AS $$
 9   DECLARE
10      erreur_class integer :=0;
		erreur_techn integer :=0;
		pas_erreur integer :=0;
	
11  BEGIN
12     select sla_code  from t_auditoutline where sla_date between timestamp'dateDebut' and timestamp'dateFin' and sla_code<>" ";
			
			if sla_code="030" or sla_code="060" then  erreur_techn+1;
			case when sla_code="060" then  erreur_techn+1
			case when sla_code='N' then  
			else 'Autres Erreurs '
			end
		from t_auditoutline;
		
		END;

select count(erreur_techn) as sum_err_tech, count(erreur_class) as sumErrTech, count(sansErr) as sumSansError
	where erreur_techn = select 



&& 
26
27  END;
28  $$ LANGUAGE plpgsql;
//(sla_err_code,erreur_class,noerror) 
select sla_err_code,
		case when (sla_code="030") OR (sla_code="030") then erreur_techn= erreur_techn+1
		     when sla_code=" " then noerror= noerror+1
			  else erreur_class = erreur_class +1
		end 
	from t_auditoutline;

	end
from t_auditoutline;

select sla_err_code from t_auditoutline where sla_err_code=	

SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

-------------------------------
select avg(sla_exec_millis) as moyenne_temps_exec,sum(sla_exec_millis) 
as sum_tps_exec from t_auditoutline where sla_date between '2013-06-29 16:38:32.098+0200'
 and '2013-07-01 00:43:44.860+0200' and sla_service='AQ';
	


Nombre d''appel par heure

---------------------------------------------
-- Procedure a compiler avant
---------------------------------------------

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;

CREATE OR REPLACE FUNCTION percentile_cont(myarray real[], percentile real)
RETURNS real AS
$$

DECLARE
  ary_cnt INTEGER;
  row_num real;
  crn real;
  frn real;
  calc_result real;
  new_array real[];
BEGIN
  ary_cnt = array_length(myarray,1);
  row_num = 1 + ( percentile * ( ary_cnt - 1 ));
  new_array = array_sort(myarray);

  crn = ceiling(row_num);
  frn = floor(row_num);

  if crn = frn and frn = row_num then
    calc_result = new_array[row_num];
  else
    calc_result = (crn - row_num) * new_array[frn] 
            + (row_num - frn) * new_array[crn];
  end if;

  RETURN calc_result;
END;
$$
  LANGUAGE 'plpgsql' IMMUTABLE;
  
  ------------------------------------------
  -- Requete 
  ------------------------------------------

  --todo faire marcher le percentile
select sla_service, 
--percentile_cont(array_agg(sla_exec_millis), 0.90), 
100 as percentile,
avg(sla_exec_millis) as average, 
count(*) as count_service, 
count(*)/(EXTRACT(EPOCH FROM max(sla_date)-min(sla_date))/3600) as count_by_hour,
--todo err code vide  mettre en null a l import
sum(case when sla_err_code != ' ' and sla_err_code != '0060' and sla_err_code != '0030' then 1 else 0 end)*100.0/count(*) as err_business,
sum(case when sla_err_code = '0060' or sla_err_code = '0030' then 1 else 0 end)*100.0/count(*) as err_tech  
from t_auditoutline 
where sla_date between '2013-06-29 16:38:32.098+0200' and '2013-07-01 00:43:44.860+0200'
--and sla_service='AQ' 
group by sla_service;



select sla_service, count(*) from t_auditoutline where sla_date between '2013-06-29 16:38:32.098+0200'
and '2013-07-01 00:43:44.860+0200' and sla_service='AQ' group by sla_service;

Nombre d''erreur classique:

select sla_service, sla_err_code,count(sla_err_code)  from t_auditoutline 
where sla_date between '2013-06-29 16:38:32.098+0200' and '2013-07-01 00:43:44.860+0200' and sla_service='AQ' and 
(sla_err_code='0060' or sla_err_code='0030')  group by sla_service,sla_err_code;


select id,sla_service, sla_err_code  from t_auditoutline 
where sla_date between '2013-06-29 16:38:32.098+0200' and '2013-07-01 00:43:44.860+0200' and sla_service='AQ' and 
(sla_err_code='0060' or sla_err_code='0030')  group by sla_service,sla_err_code,id;



select sla_service, sla_err_code,count(sla_err_code)  from t_auditoutline 
where sla_date between '2013-06-29 16:38:32.098+0200' and '2013-07-01 00:43:44.860+0200' and sla_service='AQ' and 
(sla_err_code!='0060' and sla_err_code!='0030' and sla_err_code!='')  group by sla_service,sla_err_code;



select sla_service, sla_err_code,count(sla_err_code)  from t_auditoutline 
where sla_date between '2013-06-29 16:38:32.098+0200' and '2013-07-01 00:43:44.860+0200' and sla_service='AQ' and 
(sla_err_code!='0060' and sla_err_code!='0030' and sla_err_code!=' ')  group by sla_service,sla_err_code;



---------------for percentille ----------------
CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;

CREATE OR REPLACE FUNCTION percentile_cont(myarray real[], percentile real)
RETURNS real AS
$$

DECLARE
  ary_cnt INTEGER;
  row_num real;
  crn real;
  frn real;
  calc_result real;
  new_array real[];
BEGIN
  ary_cnt = array_length(myarray,1);
  row_num = 1 + ( percentile * ( ary_cnt - 1 ));
  new_array = array_sort(myarray);

  crn = ceiling(row_num);
  frn = floor(row_num);

  if crn = frn and frn = row_num then
    calc_result = new_array[row_num];
  else
    calc_result = (crn - row_num) * new_array[frn] 
            + (row_num - frn) * new_array[crn];
  end if;

  RETURN calc_result;
END;
$$
  LANGUAGE 'plpgsql' IMMUTABLE;
  

select
sla_err_code as error,
sla_service  as service, 
sla_method as method,
		 
		count(sla_err_code)/count(sla_service)	as pourcentage,
	    sla_err_msg as message
from t_auditoutline 
where sla_err_code <> ' ' 
group by error,service, method,message ;

select  sum(case when sla_err_code != ' ' then 1 else 0 end)*100.0/count(*) as pourcentage 
from t_auditoutline 

---------------------ca donne le pourcentage de 100% ---------------------------
select sla_service as service,
	sla_method as method,
	sla_err_code as error_code,
	sum(case when sla_err_code != ' ' then 1 else 0 end)*100.0/count(*) as pourcentage ,
	sla_err_msg
from  t_auditoutline
where sla_err_code<>' ' group by service,method,error_code,sla_err_msg; 

 
	
select	sla_service as service,
	sla_method as method,
	sum(case when sla_err_code != ' ' then 1 else 0 end)*100.0/count(*) as pourcentage 
from  t_auditoutline
 group by service,method
 order by 1,2; 
 
 
  select	sla_service as service,
	sla_method as method,
	sla_err_code as err_code,
	count(case when sla_err_code != ' ' then 1 else 0 end) as nb_err
from  t_auditoutline
 group by service,method,err_code order by 1,2; 
 
 
 select	b.sla_service as service,
	b.sla_method as method,
	b.sla_err_code as err_code,
	count(b.*) as nb_err,
	count(b.*) * 100.0
	/
		(select count(a.*) 
			from t_auditoutline a 
			where a.sla_service=b.sla_service and a.sla_method=b.sla_method) as pourcentage
from  t_auditoutline b
 where sla_err_code != ' ' 
 group by service,method,err_code
  order by 1,2,3; 

------------ autre methode --------------------------------

select sla_err_code, 
	(select count( distinct sla_err_code) where sla_err_code<>' ')
	/(select count( sla_err_code) where sla_err_code<>' ') 
from t_auditoutline where sla_err_code<>' ' group by sla_err_code;



-----------------------------------------


select	b.sla_service as service,
	b.sla_method as method,
	b.sla_err_code as err_code,
	count(b.*) as nb_err,
	count(b.*) * 100.0
	/
		(select count(a.*) 
			from t_auditoutline a 
			where a.sla_service=b.sla_service and a.sla_method=b.sla_method) as pourcentage,
	b.sla_err_msg as message
from  t_auditoutline b
 where sla_err_code != ' ' 
 group by service,method,err_code,message
  order by 1,2,3,4; 

  
   select b.sla_service as service,  b.sla_method as method, 
   b.sla_err_code as erreurCode 
    from  t_auditoutline b  
   where b.sla_date between timestamp' 2013-06-29 16:38'  and timestamp' 2013-07-01 00:43' 
     and b.sla_service in ( 'AQ' , 'ASR' , 'CC' , 'HP' , 'RE'  )
   group by service, method,erreurCode order by 1,2;
   
   
   ,  count(b.*) * 100.0 /   
   (select count(a.*)  from t_auditoutline a   
   where a.sla_service = b.sla_service and a.sla_method = b.sla_method) as average
   
   b.sla_err_msg as libelleErreur 
   
   -----------------------------
   
   select sla_service as service,  
   		  sla_method as method, 
   		  percentile_cont(array_agg(sla_exec_millis), 0.9 ) as percentile,  
   		  avg(sla_exec_millis) as average,   
   		  count(*) as count_service,  
   		  count(*)
   		  /
   		  (EXTRACT(EPOCH FROM max(sla_date)-min(sla_date))/3600) as count_by_hour,  
   		  		sum(case when sla_err_code != ' ' 
   		 		    and sla_err_code != '0060' 
   		    	        and sla_err_code != '0030' 
   		  	then 1 else 0 end)*100.0/count(*) 
   		  	as err_business,  
   		  	sum(case when sla_err_code = '0060' 
   		  	or sla_err_code = '0030' 
   		  	then 1 else 0 end)*100.0/count(*) as err_tech  
   		  	from t_auditoutline  
   		  	where sla_date between timestamp' 2013-07-28 13:26'  and 
   		  	timestamp' 2013-07-29 16:04'and sla_service in ( 'AQ' , 'ASM' , 'ASR' , 'CC' , 'HP' , 'RE'  ) 
   		  	group by service,method order by 1,2
   		  	
   		  	
   
   